package com.gugu.procedure.dao;

import com.gugu.procedure.bean.Replenishment;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;

import java.util.List;
import java.util.Map;

public interface ReplenishmentDao extends JpaRepository <Replenishment, Long>, JpaSpecificationExecutor <Replenishment> {


    Replenishment findTopByOrderNoAndNumberAndStatus(String orderNo,String number,String status);


    //查询某个规格的补料申请数量
    @Query(value = "select  count(0)  num from   replenishment where order_no=?1  and if(?2!='',specs=?2,1=1)  and status='1' and if(?3!='',production=?3,1=1)", nativeQuery = true)
    int queryReplenishmentCount(String orderNo, String specs, String production);

    void deleteAllByOrderNo(String orderNo);


    @Query(value = "select  \n" +
            "a.id,\n" +
            "a.order_no,\n" +
            "a.number,\n" +
            "a.production,\n" +
            "a.reset_production,\n" +
            "a.specs,\n" +
            "a.staff_name,\n" +
            "a.dept,\n" +
            "a.reason,\n" +
            "a.use_specs,\n" +
            "a.angle,\n" +
            "a.equipment_name,\n" +
            "a.equipment_number,\n" +
            "a.status, a.check_time,a.create_time,a.check_person_name,\n" +

            "b.tolerance_up,\n" +
            "b.tolerance_down,\n" +
            "b.shell_size,\n" +
            "b.rough_supplier,\n" +
            "b.rough_item_no,\n" +
            "b.compress_up,\n" +
            "b.compress_down,\n" +
            "b.dingjing_up,\n" +
            "b.dingjing_down,\n" +
            "c.laser_param_up1,\n" +
            "c.laser_param_up2,\n" +
            "c.laser_param_down1,\n" +
            "c.laser_param_down2,\n" +
            "c.laser_param_angle\n" +
            "from  replenishment a ,order_item b ,parameter c where  a.order_no=b.order_no and a.specs=b.specs and a.order_no=c.order_no\n" +
            "and  a.specs=c.specs " +
            "and   if(coalesce (?1 , null) is not null, a.dept in (?1), 1=1) " +
            "and if(?2!=''  ,a.status=?2,1=1) " +
            "and if(?3!='',left(a.create_time,7)=?3,1=1) " +
            "and  if(?4!=''  ,a.order_no=?4,1=1) " +
            "and if(?5!='' ,a.staff_name=?5,1=1) " +
            "order by a.create_time desc " +
            " limit ?6,?7", nativeQuery = true)
    List <Map <String, Object>> pageAdmin(List <String> dept, String status, String month, String orderNo, String staffName, int pageNo, int pageSize);

    @Query(value = "select  count(1) " +
            "from  replenishment a ,order_item b ,parameter c where  a.order_no=b.order_no and a.specs=b.specs and a.order_no=c.order_no\n" +
            "and  a.specs=c.specs " +
            "and  if(coalesce (?1 , null) is not null, a.dept in (?1), 1=1) " +
            "and if(?2!='',a.status=?2,1=1) " +
            "and if(?3!='',left(a.create_time,7)=?3,1=1) " +
            "and  if(?4!='',a.order_no=?4,1=1) " +
            "and if(?5!='',a.staff_name=?5,1=1) "
            , nativeQuery = true)
    long countAdmin(List <String> dept, String status, String month, String orderNo, String staffName);


    @Query(value = "select  \n" +
            "a.id,\n" +
            "a.order_no,\n" +
            "a.number,\n" +
            "a.production,\n" +
            "a.reset_production,\n" +
            "a.specs,\n" +
            "a.staff_name,\n" +
            "a.dept,\n" +
            "a.reason,\n" +
            "a.use_specs,\n" +
            "a.angle,\n" +
            "a.equipment_name,\n" +
            "a.equipment_number,\n" +
            "a.status, a.check_time,a.create_time,a.check_person_name,\n" +

            "b.tolerance_up,\n" +
            "b.tolerance_down,\n" +
            "b.shell_size,\n" +
            "b.rough_supplier,\n" +
            "b.rough_item_no,\n" +
            "b.compress_up,\n" +
            "b.compress_down,\n" +
            "b.dingjing_up,\n" +
            "b.dingjing_down,\n" +
            "c.laser_param_up1,\n" +
            "c.laser_param_up2,\n" +
            "c.laser_param_down1,\n" +
            "c.laser_param_down2,\n" +
            "c.laser_param_angle\n" +
            "from  replenishment a ,order_item b ,parameter c where  a.order_no=b.order_no and a.specs=b.specs and a.order_no=c.order_no\n" +
            "and  a.specs=c.specs " +
            "and a.dept!='技术' " +
            "and   if(coalesce (?1 , null) is not null, a.dept in (?1), 1=1) " +
            "and if(?2!=''  ,a.status=?2,1=1) " +
            "and if(?3!='',left(a.create_time,7)=?3,1=1) " +
            "and  if(?4!=''  ,a.order_no=?4,1=1) " +
            "and if(?5!='' ,a.staff_name=?5,1=1) " +
            "order by a.create_time desc " +
            " limit ?6,?7", nativeQuery = true)
    List <Map <String, Object>> pageQc(List <String> dept, String status, String month, String orderNo, String staffName, int pageNo, int pageSize);

    @Query(value = "select  count(1) " +
            "from  replenishment a ,order_item b ,parameter c where  a.order_no=b.order_no and a.specs=b.specs and a.order_no=c.order_no\n" +
            "and  a.specs=c.specs   " +
            "and  a.dept!='技术' " +
            "and  if(coalesce (?1 , null) is not null, a.dept in (?1), 1=1) " +
            "and if(?2!='',a.status=?2,1=1) " +
            "and if(?3!='',left(a.create_time,7)=?3,1=1) " +
            "and  if(?4!='',a.order_no=?4,1=1) " +
            "and if(?5!='',a.staff_name=?5,1=1) "
            , nativeQuery = true)
    long countQc(List <String> dept, String status, String month, String orderNo, String staffName);


    @Query(value = "select  count(1) " +
            "from (select  *  from  replenishment where dept not  in ('材质','技术') or (dept='材质' and check_time is not  null) or (dept='技术' and check_time is not  null) )  a ,order_item b ,parameter c where  a.order_no=b.order_no and a.specs=b.specs and a.order_no=c.order_no\n" +
            "and  a.specs=c.specs " +
            "and if(?1!='',a.status=?1,1=1) " +
            "and if(?2!='',left(a.create_time,7)=?2,1=1) " +
            "and  if(?3!='',a.order_no=?3,1=1) " +
            "and if(?4!='',a.staff_name=?4,1=1) "
            , nativeQuery = true)
    long countWarehouse(String status, String month, String orderNo, String staffName);


    @Query(value = "select  \n" +
            "a.id,\n" +
            "a.order_no,\n" +
            "a.number,\n" +
            "a.production,\n" +
            "a.reset_production,\n" +
            "a.specs,\n" +
            "a.staff_name,\n" +
            "a.dept,\n" +
            "a.reason,\n" +
            "a.use_specs,\n" +
            "a.angle,\n" +
            "a.equipment_name,\n" +
            "a.equipment_number,\n" +
            "a.status, a.check_time,a.create_time,a.check_person_name,\n" +

            "b.tolerance_up,\n" +
            "b.tolerance_down,\n" +
            "b.shell_size,\n" +
            "b.rough_supplier,\n" +
            "b.rough_item_no,\n" +
            "b.compress_up,\n" +
            "b.compress_down,\n" +
            "b.dingjing_up,\n" +
            "b.dingjing_down,\n" +
            "c.laser_param_up1,\n" +
            "c.laser_param_up2,\n" +
            "c.laser_param_down1,\n" +
            "c.laser_param_down2,\n" +
            "c.laser_param_angle\n" +
            "from  (select  *  from  replenishment  where dept not  in ('材质','技术') or (dept='材质' and check_time is not  null) or (dept='技术' and check_time is not  null) )  a ,order_item b ,parameter c where  a.order_no=b.order_no and a.specs=b.specs and a.order_no=c.order_no\n" +
            "and  a.specs=c.specs " +
            "and if(?1!=''  ,a.status=?1,1=1) " +
            "and if(?2!='',left(a.create_time,7)=?2,1=1) " +
            "and  if(?3!=''  ,a.order_no=?3,1=1) " +
            "and if(?4!='' ,a.staff_name=?4,1=1) " +
            "order by a.create_time desc " +
            " limit ?5,?6", nativeQuery = true)
    List <Map <String, Object>> pageWarehouse(String status, String month, String orderNo, String staffName, int pageNo, int pageSize);

    @Query(value = "select  count(1) " +
            "from (select  *  from  replenishment where dept='技术'  )  a ,order_item b ,parameter c where  a.order_no=b.order_no and a.specs=b.specs and a.order_no=c.order_no\n" +
            "and  a.specs=c.specs " +
            "and if(?1!='',a.status=?1,1=1) " +
            "and if(?2!='',left(a.create_time,7)=?2,1=1) " +
            "and  if(?3!='',a.order_no=?3,1=1) " +
            "and if(?4!='',a.staff_name=?4,1=1) "
            , nativeQuery = true)
    long countTechnology(String status, String month, String orderNo, String staffName);

    @Query(value = "select  \n" +
            "a.id,\n" +
            "a.order_no,\n" +
            "a.number,\n" +
            "a.production,\n" +
            "a.reset_production,\n" +
            "a.specs,\n" +
            "a.staff_name,\n" +
            "a.dept,\n" +
            "a.reason,\n" +
            "a.use_specs,\n" +
            "a.angle,\n" +
            "a.equipment_name,\n" +
            "a.equipment_number,\n" +
            "a.status, a.check_time,a.create_time,a.check_person_name,\n" +

            "b.tolerance_up,\n" +
            "b.tolerance_down,\n" +
            "b.shell_size,\n" +
            "b.rough_supplier,\n" +
            "b.rough_item_no,\n" +
            "b.compress_up,\n" +
            "b.compress_down,\n" +
            "b.dingjing_up,\n" +
            "b.dingjing_down,\n" +
            "c.laser_param_up1,\n" +
            "c.laser_param_up2,\n" +
            "c.laser_param_down1,\n" +
            "c.laser_param_down2,\n" +
            "c.laser_param_angle\n" +
            "from  (select  *  from  replenishment where dept='技术'  )  a ,order_item b ,parameter c where  a.order_no=b.order_no and a.specs=b.specs and a.order_no=c.order_no\n" +
            "and  a.specs=c.specs " +
            "and if(?1!=''  ,a.status=?1,1=1) " +
            "and if(?2!='',left(a.create_time,7)=?2,1=1) " +
            "and  if(?3!=''  ,a.order_no=?3,1=1) " +
            "and if(?4!='' ,a.staff_name=?4,1=1) " +
            "order by a.create_time desc " +
            " limit ?5,?6", nativeQuery = true)
    List <Map <String, Object>> pageTechnology(String status, String month, String orderNo, String staffName, int i, int pageSize);


}
